package cn.shuto.liferay.plugin.chart.service.util;

import java.io.StringWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import org.apache.velocity.VelocityContext;
import org.apache.velocity.app.VelocityEngine;

import com.liferay.portal.kernel.exception.PortalException;
import com.liferay.portal.kernel.exception.SystemException;
import com.liferay.portal.kernel.json.JSONArray;
import com.liferay.portal.kernel.json.JSONFactoryUtil;
import com.liferay.portal.kernel.json.JSONObject;
import com.liferay.portal.kernel.util.StringUtil;

import cn.shuto.liferay.plugin.chart.service.model.ChartTemplate;
import cn.shuto.liferay.plugin.chart.service.model.Option;
import cn.shuto.liferay.plugin.chart.service.service.ChartTemplateLocalServiceUtil;
import cn.shuto.liferay.plugin.chart.service.service.OptionLocalServiceUtil;

public class VelocityTemplateUtil {

	public String analysisTemplateToString(long optionId, String namespace,
			String chart_view_theme,String chart_height) throws PortalException, SystemException {
		// 获取图表
		Option option = OptionLocalServiceUtil.getOption(optionId);
		// 获取图表对应的模板
		ChartTemplate template = ChartTemplateLocalServiceUtil
				.getChartTemplate(option.getChartTemplateId());

		// 获取图表参数的值
		JSONObject paramValue = JsonUtil.analysisTemplateParamValue(option
				.getTemplateParamValue());
		// 获取图表参数
		JSONArray paramArray = JsonUtil.analysisTemplateParam(template
				.getTemplateParam());
		// 取得velocity的上下文context
		VelocityContext context = new VelocityContext();
		context.put("namespace", namespace);
		context.put("chart_view_theme", chart_view_theme);
		context.put("chart_height", chart_height);
		context.put("dataSqlJson", analysisChartDataSql(option.getDataSourceId(), option.getDataSql()));
		if (paramArray.length() > 0) {
			for (int i = 0; i < paramArray.length(); i++) {
				JSONObject param = paramArray.getJSONObject(i);
				if ("boolean".equals(param.getString("paramtype"))) {
					context.put(param.getString("paramtag"),
							paramValue.getBoolean(param.getString("paramtag")));
				} else if ("number".equals(param.getString("paramtype"))) {
					context.put(param.getString("paramtag"),
							paramValue.getDouble(param.getString("paramtag")));
				} else {
					context.put(param.getString("paramtag"),
							paramValue.getString(param.getString("paramtag")));
				}
			}
		}

		if (paramValue.length() > 0) {
			JSONArray names = paramValue.names();
			for (int i = 0; i < names.length(); i++) {
				context.put(names.getString(i),
						paramValue.getString(names.getString(i)));
			}
		}

		StringWriter writer = new StringWriter();
		ve.evaluate(context, writer, "", template.getScript());
		return writer.toString();
	}

	public JSONArray analysisChartDataSql(long dataSourceId, String dataSql)
			throws PortalException, SystemException {
		JSONArray chartDataArray = null;
		Connection conn = DataSourceFactory.getConnection(StringUtil.valueOf(dataSourceId));
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			chartDataArray = JSONFactoryUtil.createJSONArray();

			statement = conn.prepareStatement(dataSql);
			rs = statement.executeQuery();
			ResultSetMetaData metaData = rs.getMetaData();
			int columnCount = metaData.getColumnCount();
			while (rs.next()) {
				JSONObject jsonObj = JSONFactoryUtil.createJSONObject();
				// 遍历每一列
				for (int i = 1; i <= columnCount; i++) {
					String columnName = metaData.getColumnLabel(i);
					String value = rs.getString(columnName);
					jsonObj.put(columnName, value);
				}
				chartDataArray.put(jsonObj);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DataSourceFactory.close(conn);
		}

		return chartDataArray;
	}


	private VelocityEngine ve;

	private static class SingletonHolder {
		/**
		 * 静态初始化器，由JVM来保证线程安全
		 */
		private static VelocityTemplateUtil instance = new VelocityTemplateUtil();
	}

	private VelocityTemplateUtil() {
		ve = new VelocityEngine();
		ve.init();
	}

	public static VelocityTemplateUtil getInstance() {
		return SingletonHolder.instance;
	}

}
